﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ChatServer.dbconn
{
    public abstract class DBConnector
    {
        private String ConnString;
        // Constructor
        public DBConnector()
        {
            ConnString = Utility.GetSqlConnString();
        }

        /*
        //Method for creating connection string for access to the Database
        private static SqlConnection GetConnection()
        {
            return null;
        }
         */

        //Method connects to database and runs stored procedure through sqlcommand.
        //Returns datareader to read results from
        public int SendWriteRequest(string _spName, List<SqlParameter> _spParams)
        {
            SqlConnection conn = null;
            try
            {
                //setup sql connection
                conn = new SqlConnection(ConnString);
                //setup a SQL commnad to run the stored procedure
                SqlCommand cmd = buildCommand(_spName, _spParams, conn);
                cmd.ExecuteNonQuery();
                int returnValue = (int)cmd.Parameters["@returnValue"].Value;

                return returnValue;
            }
            catch (SqlException ex)
            {
                return 0;
            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }

        //Method connects to database and runs a query given by the parameter. 
        //Returns datareader to read results from.
        public DataTable SendDataTableRequest(string _spName, List<SqlParameter> _spParams)
        {
            SqlConnection conn = null;
            try
            {
                //setup sql connection
                conn = new SqlConnection(ConnString);
                conn.Open(); //How about opening the connection before you use it (Mr. Evans)
                //setup a SQL commnad to run the stored procedure
                SqlCommand cmd = buildCommand(_spName, _spParams, conn);

                //execure the command using a SqlDataReader
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //create a data table and load it with the data from the SQL Data Reader
                DataTable dt = new DataTable();
                dt.Load(dr);
                //return the data requested to the user.
                return dt;
            }
            catch (SqlException ex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        private SqlCommand buildCommand(string _spName, List<SqlParameter> _spParams, SqlConnection conn)
        {
                //setup a SQL commnad to run the stored procedure
                SqlCommand cmd = new SqlCommand(_spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //add all SQL Parameters to the command
                foreach (SqlParameter param in _spParams)
                {
                    cmd.Parameters.Add(param);
                }

                return cmd;

        }
        
    }
}
